6  Working with Numeric Data

6.1 Reference

Default database in this chapter is “ex”.

Other databases included in the simulation MySQL server:

  • om
  • ap

6.2 Numeric Data

Main Types

Integer(INT type): no decimal places.

Fixed-point(DECIMAL type): up to 9 decimal places.

  • Because there are finite number of decimal places, they are exact numeric type.

Floating-point (shortly floating, DOUBLE/FLOAT type) values are numeric values with unlimited number of decimal places.

  • Because there are unlimited number of decimal places, they are approximate numeric type.

Other Concepts

Precision: number of digit (e.g. 120.98 has a precision of 5, as in 5 digits in total)

Scale: number of decimal places (e.g. 120.98 has a scale of 2, as in 2 decimal places)

6.3 Numeric Data Manipulation

Managing Rounding and Scale

SELECT 
  float_value,
  ROUND(float_value, 3),
  TRUNCATE(float_value, 3),
  CEILING(float_value),
  FLOOR(float_value)
FROM float_sample
6 records
float_value ROUND(float_value, 3) TRUNCATE(float_value, 3) CEILING(float_value) FLOOR(float_value)
1.00000 1.000 0.999 1 0
1.00000 1.000 1.000 1 1
1.00000 1.000 1.000 2 1
1234.56789 1234.568 1234.567 1235 1234
999.04440 999.044 999.044 1000 999
24.04849 24.048 24.048 25 24

Note: negative values are evaluated together with their negativity.

SELECT 
  CEILING(-12.4567),
  FLOOR(-12.4567)
1 records
CEILING(-12.4567) FLOOR(-12.4567)
-12 -13

Calcuation

SELECT 
  float_value,
  ABS(float_value),
  -- SIGN(float_value),
  SQRT(float_value),
  POWER(float_value, 3)
FROM float_sample
6 records
float_value ABS(float_value) SQRT(float_value) POWER(float_value, 3)
1.00000 1.00000 1.000000 1.000000e+00
1.00000 1.00000 1.000000 1.000000e+00
1.00000 1.00000 1.000000 1.000000e+00
1234.56789 1234.56789 35.136418 1.881676e+09
999.04440 999.04440 31.607664 9.971359e+08
24.04849 24.04849 4.903926 1.390796e+04

Random Number Generation

RAND() function doesn’t require any input to work, i.e. they will generate random numbers anyways.

The integer as an argument is called “seed”. This is used to ensure everytime the query run it will create exactly the same set of random numbers.

SELECT
  float_value,
  RAND(123)
FROM float_sample
6 records
float_value RAND(123)
1.00000 0.9277429
1.00000 0.4609716
1.00000 0.5216294
1234.56789 0.2252323
999.04440 0.5612733
24.04849 0.1306697